﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;
namespace DAO
{
    public class LichHTDAO
    {
        public List<LichHTDTO> DanhSachTheoThang(int thang,int nam)
        {
            List<LichHTDTO> ds = new List<LichHTDTO>();
            SqlConnection conn = new SqlConnection();
            conn = DataProvider.ConnectDB();

            SqlCommand cmd = new SqlCommand("proc_DanhSachTheoThang", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter sqlPar;

            sqlPar = new SqlParameter("@thang", SqlDbType.Int);
            sqlPar.Direction = ParameterDirection.Input;
            sqlPar.Value = thang;
            cmd.Parameters.Add(sqlPar);

            sqlPar = new SqlParameter("@nam", SqlDbType.Int);
            sqlPar.Direction = ParameterDirection.Input;
            sqlPar.Value = nam;
            cmd.Parameters.Add(sqlPar);

            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                LichHTDTO cal = new LichHTDTO();
                cal.MaMA = reader.GetString(0);
                cal.NgayLL = reader.GetDateTime(1);
                ds.Add(cal);
            }
            conn.Close();
            return ds;
        }
        public int MonAnChuaLenLich(string MaMA, int thang, int nam)
        {
            int kq=0;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn = DataProvider.ConnectDB();

                SqlCommand cmd = new SqlCommand("proc_MonAnChuaLenLich", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter sqlPar;
                sqlPar = new SqlParameter("@mama", SqlDbType.VarChar, 6);
                sqlPar.Direction = ParameterDirection.Input;
                sqlPar.Value = MaMA;
                cmd.Parameters.Add(sqlPar);
                sqlPar = new SqlParameter("@thang", SqlDbType.Int);
                sqlPar.Direction = ParameterDirection.Input;
                sqlPar.Value = thang;
                cmd.Parameters.Add(sqlPar);
                sqlPar = new SqlParameter("@nam", SqlDbType.Int);
                sqlPar.Direction = ParameterDirection.Input;
                sqlPar.Value = nam;
                cmd.Parameters.Add(sqlPar);
                sqlPar = new SqlParameter("@res", SqlDbType.Int);
                sqlPar.Direction = ParameterDirection.Output;
               cmd.Parameters.Add(sqlPar);


                cmd.ExecuteNonQuery();
                kq = int.Parse(cmd.Parameters["@res"].Value.ToString());
                conn.Close();
                return kq;
            }
            catch (SqlException ex)
            {
                return kq;
            }
        }
        public int KiemTraLenLich(int thang, int nam)
        {
            int kq = 0;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn = DataProvider.ConnectDB();

                SqlCommand cmd = new SqlCommand("proc_KiemTraLenLich", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter sqlPar;
               
                sqlPar = new SqlParameter("@thang", SqlDbType.Int);
                sqlPar.Direction = ParameterDirection.Input;
                sqlPar.Value = thang;
                cmd.Parameters.Add(sqlPar);

                sqlPar = new SqlParameter("@nam", SqlDbType.Int);
                sqlPar.Direction = ParameterDirection.Input;
                sqlPar.Value = nam;
                cmd.Parameters.Add(sqlPar);

                sqlPar = new SqlParameter("res", SqlDbType.Int);
                sqlPar.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(sqlPar);


                kq = cmd.ExecuteNonQuery();
                kq = int.Parse(cmd.Parameters["res"].Value.ToString());
                conn.Close();
            }
            catch (SqlException ex)
            {
                return kq;
            }
            return kq;
        }
        public int ThemMonAnHT(string MaMA,string date)
        {
            int res = 0;
            SqlConnection conn = new SqlConnection();
            conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("proc_ThemMonAnHT", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter sqlPar;

            sqlPar = new SqlParameter("@mama", SqlDbType.VarChar, 6);
            sqlPar.Direction = ParameterDirection.Input;
            sqlPar.Value = MaMA;
            cmd.Parameters.Add(sqlPar);

            sqlPar = new SqlParameter("@ngayll", SqlDbType.DateTime);
            sqlPar.Direction = ParameterDirection.Input;
            sqlPar.Value = date;
            cmd.Parameters.Add(sqlPar);

            sqlPar = new SqlParameter("@res", SqlDbType.Int);
            sqlPar.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(sqlPar);

            res = cmd.ExecuteNonQuery();
            res= int.Parse(cmd.Parameters["@res"].Value.ToString());
            conn.Close();
            return res;
        }

        //public int MaChaMay(int thang, int nam)
        //{
        //    int kq;
        //    SqlConnection conn = new SqlConnection();
        //    conn = DataProvider.ConnectDB();

        //    SqlCommand cmd = new SqlCommand("proc_SoLuongLoaiMonAn", conn);
        //    cmd.CommandType = CommandType.StoredProcedure;

        //    SqlParameter sqlPar;

        //    sqlPar = new SqlParameter("thang", SqlDbType.Int);
        //    sqlPar.Direction = ParameterDirection.Input;
        //    sqlPar.Value = thang;
        //    cmd.Parameters.Add(sqlPar);

        //    sqlPar = new SqlParameter("nam", SqlDbType.Int);
        //    sqlPar.Direction = ParameterDirection.Input;
        //    sqlPar.Value = nam;
        //    cmd.Parameters.Add(sqlPar);

        //    sqlPar = new SqlParameter("sl", SqlDbType.Int);
        //    sqlPar.Direction = ParameterDirection.Output;
        //    cmd.Parameters.Add(sqlPar);


        //   cmd.ExecuteNonQuery();
        //   kq = int.Parse(cmd.Parameters["sl"].Value.ToString());
        //    conn.Close();
        //    return kq;
        //}
    }
}
